
using System;
using System.Linq;
using System.Collections.Generic;
using MonoTouch.Foundation;
using System.IO;
using System.Threading;

namespace ComBrowser
{


	public class ComDomain
	{

		private ComInfo info = null;
		private static object locker = new object ();

		static ComDomain singleton = null;

		#region CTOR
		public static ComDomain Instance ()
		{
			lock (locker) {
				if (singleton == null)
					singleton = new ComDomain ();
			}
			return singleton;
		}

		private ComDomain ()
		{
			Console.WriteLine ("Building ComDomain");
			if (info == null) {
				CopyDBtoDocFolder ();
				//done only once to get the info
				using (var db = new SQLiteConnection (Settings.DatabasePath)) {
					db.Open ();
					var q = db.Query<ComInfo> ("SELECT ShortName,LongName,Language,Description,HasOldTestament,HasNewTestament,HasVerse FROM tblInfo Limit 1");
					info = q.ToList ()[0];
					Console.WriteLine ("Built ComDomain");	
				}
			}
		}
		#endregion

		#region Property: OneBasedBookIDs
		private List<int> oneBasedBookIDs = new List<int> ();
		public List<int> OneBasedBookIDs {
			get {
				if (oneBasedBookIDs.Count == 0) {
					using (var db = new SQLiteConnection (Settings.DatabasePath)) {
						db.Open ();
						
						var q = db.Query<QuerySimpleID> ("SELECT Distinct Book as ID FROM tblMain");
						
						foreach (QuerySimpleID i in q.ToList ())
							oneBasedBookIDs.Add (i.ID);
					}
				}
				return oneBasedBookIDs;
			}
		}
		#endregion

		#region Helper: BookHasChapter (int ZeroBasedBookID, int ZeroBasedChapterID)
		public HashSet<string> bookChapter = new HashSet<string> ();
		public bool BookHasChapter (int ZeroBasedBookID, int ZeroBasedChapterID)
		{
			
			string bookHasBeenGottenKey = ZeroBasedBookID.ToString ();
			string key = "000" + ZeroBasedBookID + "-" + ZeroBasedChapterID;
			if (!bookChapter.Contains (bookHasBeenGottenKey)) {
				bookChapter.Add (bookHasBeenGottenKey);
				
				//fill the book with chapyters
				using (var db = new SQLiteConnection (Settings.DatabasePath)) {
					db.Open ();
					
					var q = db.Query<QuerySimpleID> (string.Format ("SELECT Distinct Chapter as ID FROM tblMain where book={0}", (ZeroBasedBookID + 1)));
					
					foreach (QuerySimpleID i in q.ToList ()) {
						string keyToAdd = "000" + ZeroBasedBookID + "-" + (i.ID - 1).ToString ();
						bookChapter.Add (keyToAdd);
					}
				}
			}
			
			return bookChapter.Contains (key);
		}
		#endregion

		#region Helper: GetCommentaryHTML (int ZeroBasedBook, int ZeroBasedChapter)
		public string GetCommentaryHTML (int ZeroBasedBook, int ZeroBasedChapter)
		{
			
			if (ZeroBasedBook < 0 || ZeroBasedChapter < 0)
				throw new Exception ("GetCommentaryHTML passed a negative.");
			
			string retVal = "";
			using (var db = new SQLiteConnection (Settings.DatabasePath)) {
				string verseOrderClause = "";
				if (info.HasVerse == 1) {
					verseOrderClause = " order by verse ";
				}
				
				db.Open ();
				var q = db.Query<Commentary> (string.Format ("SELECT  ID,Book,Chapter,Verse,Body,BodyText FROM tblMain where book={0} and chapter={1} " + verseOrderClause, ZeroBasedBook + 1, ZeroBasedChapter + 1));
				foreach (var o in q.ToList<Commentary> ()) {
					retVal += "<span onclick='ClickGeneralArea();'>" + o.Body + "</span></font>";
				}
			}
			
			string title = "<h1>" + GetTitle (ZeroBasedBook, ZeroBasedChapter) + "</h1>";
			return title + retVal;
		}
		#endregion

		#region Helper: CopyDBtoDocFolder
		void CopyDBtoDocFolder ()
		{
			
			if (!Settings.HasDBBeenInstalled) {
				
				//Create destination folder
				string destFolder = Path.Combine (Environment.GetFolderPath (Environment.SpecialFolder.Personal), "DB");
				string destDB = Path.Combine (destFolder, "Active.db3");
				
				if (!Directory.Exists (destFolder))
					Directory.CreateDirectory (destFolder);

				if (!File.Exists (destDB)) {
					string sourceFolder = Path.Combine (NSBundle.MainBundle.BundlePath, "Deploy");
					
					//Get the first DB in the Source Deploy folder
					string[] databases = Directory.GetFiles (sourceFolder, "*.db3");
					
					if (databases.Count () > 0)
					{
						string sourceDB = Path.Combine (sourceFolder, databases[0]);
						File.Copy (sourceDB, destDB);
						Settings.HasDBBeenInstalled = true;
					}
				}
				
			}
		}
		#endregion

		#region Helper: ComInfo
		public ComInfo Info {
			get { return this.info; }
		}
		#endregion

		#region Helper: GetVeryFirstBookChapter (ref int book, ref int chapter)
		public void GetVeryFirstBookChapter (ref int zeroBasedBook, ref int zeroBasedChapter)
		{
			using (var db = new SQLiteConnection (Settings.DatabasePath)) {
				string verseOrderClause = "";
				if (info.HasVerse == 1) {
					verseOrderClause = " order by verse ";
				}
				
				db.Open ();
				var q = db.Query<Commentary> (string.Format ("SELECT ID, Book,Chapter,Verse,Body,BodyText FROM tblMain order by book,chapter limit 1"));
				foreach (var o in q.ToList()) {
					zeroBasedBook = o.Book - 1;
					zeroBasedChapter = o.Chapter - 1;
					break;
				}
			}
		}
		#endregion

		#region Helper: GetTitle (int ZeroBasedSelectedBookID, int ZeroBasedSelectedChapterID)
		public string GetTitle (int ZeroBasedSelectedBookID, int ZeroBasedSelectedChapterID)
		{
			BookCollection bc = BookCollection.Instance ();
			
			string bookName = bc[ZeroBasedSelectedBookID].Long;
			if (bc[ZeroBasedSelectedBookID].Long.Length > 10)
				bookName = bc[ZeroBasedSelectedBookID].Short;
			
			return bookName + " " + (ZeroBasedSelectedChapterID + 1).ToString ("d");
			
		}
		#endregion

		#region Helper: GetNextChapterIDs (ref int zeroBasedBookID, ref int zeroBasedChapterID)
		public void GetNextChapterIDs (ref int zeroBasedBookID, ref int zeroBasedChapterID)
		{
			using (var db = new SQLiteConnection (Settings.DatabasePath)) {
				db.Open ();
				
				string sql = string.Format ("SELECT  ID FROM tblMain where book={0} and chapter={1} order by book, chapter, verse desc limit 1", (zeroBasedBookID + 1), (zeroBasedChapterID + 1));
				var q = db.Query<QuerySimpleID> (sql);
				List<QuerySimpleID> results = q.ToList ();
				if (results != null && results.Count > 0) {
					int lastIDOfChapter = results[0].ID;
					lastIDOfChapter++;
					var next = db.Query<Commentary> (string.Format ("SELECT  ID,Book,Chapter,Verse,Body,BodyText FROM tblMain where ID={0} limit 1", lastIDOfChapter));
					var items = next.ToList ();
					if (items != null && items.Count == 1) {
						zeroBasedBookID = items[0].Book - 1;
						zeroBasedChapterID = items[0].Chapter - 1;
					}
				}
			}
		}
		#endregion

		#region Helper: GetPreviousChapterIDs (ref int zeroBasedBookID, ref int zeroBasedChapterID)
		public void GetPreviousChapterIDs (ref int zeroBasedBookID, ref int zeroBasedChapterID)
		{
			using (var db = new SQLiteConnection (Settings.DatabasePath)) {
				db.Open ();
				
				string sql = string.Format ("SELECT  ID FROM tblMain where book={0} and chapter={1} order by book, chapter, verse limit 1", (zeroBasedBookID + 1), (zeroBasedChapterID + 1));
				var q = db.Query<QuerySimpleID> (sql);
				List<QuerySimpleID> results = q.ToList ();
				if (results != null && results.Count > 0) {
					int previousIDOfChapter = results[0].ID;
					previousIDOfChapter--;
					var next = db.Query<Commentary> (string.Format ("SELECT  ID,Book,Chapter,Verse,Body,BodyText FROM tblMain where ID={0} limit 1", previousIDOfChapter));
					var items = next.ToList ();
					if (items != null && items.Count == 1) {
						zeroBasedBookID = items[0].Book - 1;
						zeroBasedChapterID = items[0].Chapter - 1;
					}
				}
			}
		}
		#endregion

		#region Helper: Search (int Scope, string Query)
		public Dictionary<SearchResultSection, List<Commentary>> Search (int Scope, string Query)
		{

			Dictionary<SearchResultSection, List<Commentary>> retVal = new Dictionary<SearchResultSection, List<Commentary>> ();
			
			using (var db = new SQLiteConnection (Settings.DatabasePath)) {
				string verseOrderClause = "";
				if (info.HasVerse == 1) {
					verseOrderClause = " order by verse ";
				}
				
				db.Open ();
				var q = db.Query<Commentary> (string.Format (@"
				SELECT  ID,Book,Chapter,Verse,Body,BodyText	
				FROM tblMain	
				where BodyText like '%{0}%' order by book,chapter,verse", Query));
				
				SearchResultSection section = null;

				
				BookCollection books = BookCollection.Instance ();
				int index = 0;
				int resultID = 0;
				foreach (Commentary item in q.ToList ())
				{
					item.ResultID = resultID++;
					int zeroBasedBookID = item.Book - 1;
					if (section == null) {
						section = new SearchResultSection ();
						section.Title = books[zeroBasedBookID].Long;
						section.ResultCount = 0;
						section.Index = 0;
						section.ZeroBasedBookID = zeroBasedBookID;
						retVal.Add (section, new List<Commentary> ());
					}
					
					if (section.ZeroBasedBookID != zeroBasedBookID)
					{
						section = new SearchResultSection ();
						section.Title = books[zeroBasedBookID].Long;
						section.ResultCount = 0;
						section.ZeroBasedBookID = zeroBasedBookID;
						section.Index = ++index;
						retVal.Add (section, new List<Commentary> ());
					}
				 
					section.ResultCount++;
					retVal[section].Add (item);
					 	
				}
			}
			
			return retVal;
		}
		#endregion
	}
	

}
